package com.example.graduationdesign.mapper;

import com.example.graduationdesign.pojo.dao.ArticleDao;
import com.example.graduationdesign.pojo.dao.UserDao;
import com.example.graduationdesign.pojo.dao.WordDao;
import com.example.graduationdesign.pojo.dto.*;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface AdminMapper {

    /**
     * 用于获取用户的全部信息
     * @param pageNo
     * @param pageSize
     * @return
     */
    List<UserDao> SelectUserInfo(@Param("pageNo") Integer pageNo, @Param("pageSize") Integer pageSize);


    /**
     * 用于获取文章的全部信息
     * @param pageNo
     * @param pageSize
     * @return
     */
    List<ArticleDao> SelectArticleInfo(@Param("pageNo") Integer pageNo,@Param("pageSize") Integer pageSize);
    /**
     * 用于获取注册用户的个数
     * @return
     */
    Integer GetUserCount();

    /**
     * 用于获取文章的个数
     * @return
     */
    Integer GetArticleCount();

    /**
     * 用于获取单词的全部信息
     * @param pageNo
     * @param pageSize
     * @return
     */
    List<WordDao> SelectWordInfo(@Param("pageNo") Integer pageNo, @Param("pageSize") Integer pageSize);


    /**
     * 用于获取单词的数量
     * @return
     */
    Integer GetWordCount();


    /**
     * 用于获取活跃用户数量
     * @return
     */
    Integer GetStudentCount();


    /**
     * 用于获取今日打卡人数
     * @return
     */
    Integer GetCheckCount();


    /**
     * 用于管理员对文章进行查询
     * @param articleId
     * @return
     */
    List<ArticleDao> SearchArticle(@Param("articleId") Long articleId);

    /**
     * 用于新增文章的操作
     * @param title
     * @param content
     * @param author
     * @param image
     * @return
     */
    @Insert("insert into article(article_image,article_author,article_content,article_name) values(#{image},#{author},#{content},#{title});")
    Integer InsertArticle(@Param("title") String title,@Param("content") String content,@Param("author") String author,@Param("image") String image);


    /**
     * 用于有图片修改的
     * @param title
     * @param content
     * @param author
     * @param image
     * @param code
     * @return
     */
    @Update("update article set article_image = #{image},article_author = #{author},article_content = #{content},article_name = #{title},update_time = NOW() WHERE article_code =#{code}; ")
    Integer UpdateArticleOne(@Param("title") String title,@Param("content") String content,@Param("author") String author,@Param("image") String image,@Param("code") Long code);


    /**
     * 用于无图片修改的编辑中
     * @param title
     * @param content
     * @param author
     * @param code
     * @return
     */
    @Update("update article set article_author = #{author},article_content = #{content},article_name = #{title},update_time = NOW() WHERE article_code =#{code}; ")
    Integer UpdateArticleTwo(@Param("title") String title,@Param("content") String content,@Param("author") String author,@Param("code") Long code);

    /**
     * 用于删除文章内容
     * @param code
     * @return
     */
    @Delete("DELETE FROM article\n" +
            "WHERE article_code = #{code};")
    Integer DeleteArticle(@Param("code") Long code);


    /**
     * 用于统计用户在某一阶段的排名
     * @param stage
     * @return
     */
    @Select("SELECT\n" +
            "    u.user_id AS userId,\n" +
            "    u.user_name AS name,\n" +
            "    w.word_stage AS stage, COUNT(sr.studyrecord_code) AS wordCount,\n" +
            "    u.create_time AS createTime\n" +
            "FROM\n" +
            "    english_study.user u\n" +
            "        JOIN\n" +
            "    english_study.consumer c ON u.user_id = c.consumer_id\n" +
            "        JOIN\n" +
            "    english_study.studyrecord sr ON c.consumer_id = sr.studyrecord_consumer_id\n" +
            "        JOIN\n" +
            "    english_study.wordlist w ON sr.studyrecord_code = w.code\n" +
            "WHERE\n" +
            "        w.word_stage = #{stage} -- 单词阶段为小学阶段\n" +
            "  AND sr.studyed_is_familarized = 1 AND u.role_id != 102 -- 单词是否熟悉（1 表示熟悉）\n" +
            "GROUP BY\n" +
            "    u.user_id, u.user_name, w.word_stage\n" +
            "ORDER BY\n" +
            "    wordCount DESC\n" +
            "LIMIT 10;")
    List<RankDto> GetStudentRanking(@Param("stage") Integer stage);


    /**
     * 要用于管理员修改用户的基本信息
     * @param userId
     * @param sex
     * @param age
     * @param userName
     * @param phone
     * @param email
     * @return
     */
    @Update("UPDATE user set user_name = #{userName},age = #{age}, sex = #{sex},phone = #{phone},email = #{email} WHERE user_id = #{userId};")
    Integer UpdateUserInfo(@Param("userId") Long userId,@Param("sex") Integer sex,@Param("age") Integer age,@Param("userName") String userName,@Param("phone") String phone,@Param("email") String email);


    /**
     * 用于管理员逻辑删除用户的基本信息
     * @param userId
     * @param isActive
     * @return
     */
    @Update("UPDATE user set is_active = #{isActive} WHERE user_id = #{userId};")
    Integer DeleteUserInfo(@Param("userId") Long userId, @Param("isActive") Integer isActive);


    /**
     * 用于查询用户的基本信息
     * @param userId
     * @param userName
     * @return
     */
    @Select("SELECT * from user where user_id = #{userId} OR user_name =#{userName};")
    List<UserDao> SearchUserInfo(@Param("userId") Long userId,@Param("userName") String userName);


    /**
     * 用于管理员更新单词信息的SQL
     * @param wordDao
     * @return
     */
    @Update("UPDATE wordlist set word = #{wordDao.word}," +
            "phonetic=#{wordDao.phonetic},interpretation = #{wordDao.interpretation}," +
            "split = #{wordDao.split},synthesis = #{wordDao.synthesis}," +
            "associative = #{wordDao.associative},example = #{wordDao.example}," +
            "translate = #{wordDao.translate},word_difficulty=#{wordDao.wordDifficulty}," +
            "word_stage=#{wordDao.wordStage},update_time = NOW() WHERE code = #{wordDao.code}")
    Integer UpdateWordInfo(@Param("wordDao") WordDao wordDao);


    /**
     * 用于管理员添加单词基本信息的SQL
     * @param wordDao
     * @return
     */
    @Insert("INSERT wordlist(word,phonetic,interpretation,split,synthesis,associative,example," +
            "translate,word_difficulty,word_stage) values (#{wordDao.word},#{wordDao.phonetic},#{wordDao.interpretation}," +
            "#{wordDao.split},#{wordDao.synthesis},#{wordDao.associative},#{wordDao.example},#{wordDao.translate},#{wordDao.wordDifficulty}," +
            "#{wordDao.wordStage})")
    Integer AddWordInfo(@Param("wordDao") WordDao wordDao);


    /**
     * 按照单词查询的SQL
     * @param searchInput
     * @return
     */
    @Select("select * from wordlist where word = #{searchInput}; ")
    List<WordDao> SearchWordInfoByName(@Param("searchInput") String searchInput);


    /**
     * 按照单词难度查询
     * @param searchInput
     * @return
     */
    @Select("select * from wordlist where word_difficulty = #{searchInput}; ")
    List<WordDao> SearchWordInfoByDifficulty(@Param("searchInput") String searchInput);

    /**
     * 按照单词阶段查询
     * @param searchInput
     * @return
     */
    @Select("select * from wordlist where word_stage = #{searchInput}; ")
    List<WordDao> SearchWordInfoByStage(@Param("searchInput") String searchInput);


    @Select("SELECT DATE(create_time) AS date, COUNT(DISTINCT user_id) AS value\n" +
            "FROM user\n" +
            "WHERE is_active = 0\n" +
            "  AND create_time BETWEEN #{startDate} AND #{endDate}\n" +
            "GROUP BY DATE(create_time);")
    List<BarChartDto> GetBarChartData(@Param("startDate") String startDate,@Param("endDate") String endDate);



    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m') AS date, COUNT(*) AS value\n" +
            "FROM user " +
            " WHERE create_time BETWEEN #{startDate} AND #{endDate}\n" +
            "GROUP BY DATE_FORMAT(create_time, '%Y-%m');")
    List<LineChartDto> GetLineChartData(@Param("startDate") String startDate, @Param("endDate") String endDate);


    @Select("SELECT \n" +
            "  CASE \n" +
            "    WHEN consumer_variety = 0 THEN '普通用户'\n" +
            "    WHEN consumer_variety = 1 THEN '会员用户'\n" +
            "  END AS name,\n" +
            "  COUNT(*) AS value\n" +
            "FROM user,consumer WHERE user.user_id = consumer.consumer_id \n" +
            "GROUP BY consumer_variety;")
    List<PieChartDto> GetUserVarietyData();


    @Select("SELECT \n" +
            "  SUM(CASE WHEN studyed_is_like = 1 THEN 1 ELSE 0 END) AS likes,\n" +
            "  SUM(CASE WHEN studyed_is_favorite = 1 THEN 1 ELSE 0 END) AS favorites,\n" +
            "  SUM(CASE WHEN studyed_is_studyed = 1 THEN 1 ELSE 0 END) AS mastered,\n" +
            "  SUM(CASE WHEN studyed_is_familarized = 1 THEN 1 ELSE 0 END) AS need_learn\n" +
            "FROM studyrecord\n" +
            "WHERE DATE(studyrecord_time) BETWEEN #{startDate} AND #{endDate};")
    RadarChartDto GetUserAction(@Param("startDate") String startDate, @Param("endDate") String endDate);


    /**
     * 用于实现获取某个地区用户人数
     * @return
     */
    @Select("select city as name,count(DISTINCT user_id) as value from user Group By city;")
    List<CityDto> GetCity();

    /**
     * 用于判断其中的单词是否有多次存储，防止两个单词被存储两次
     *
     * @param word
     * @return
     */
    @Select("SELECT COUNT(*) FROM wordlist WHERE word = #{word};")
    Integer IsExistWord(@Param("word") String word);


    /**
     * 用于修改管理员的验证码
     * @param modifyAdminEmailDto
     * @return
     */
    @Update("update user set email = #{modifyAdminEmailDto.newEmail} where user_id = #{modifyAdminEmailDto.userId};")
    Integer updateAdminEmailInformation(@Param("modifyAdminEmailDto")ModifyAdminEmailDto modifyAdminEmailDto);
}
